'''
Description: 本地数据库模块
Author: HowsenFisher
Date: 2021-04-07 09:12:07
LastEditTime: 2021-04-11 01:43:03
LastEditors: liutq
Reference: 
'''
from sqlalchemy import *
from sqlalchemy.ext.declarative import *
from sql.SQL import *
from sqlalchemy.orm import *
import json
from Config import conf

# 获取Base
Base = declarative_base()

'''
@description: 获取session函数
@param  {*}
@return {*}
'''
def makeSession():
    Base = declarative_base()
    this_engine = getWordDB()
    MessageSession = sessionmaker(bind=this_engine)
    message_session = MessageSession()
    return Base,this_engine,message_session


class Device(Base):
    '''
    @description: 设备表
    @param  {*}
    @return {*}
    '''
    # 表名：device
    __tablename__ = 'device'
    # 所有列
    device_id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(128))
    ip = Column(String)

    '''
    @description: 查询结果转换json函数
    @param  {*}
    @return {*}
    @param {*} self
    '''
    def to_json(self):
        dict = self.__dict__
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict


class Message(Base):
    '''
    @description: 消息表
    @param  {*}
    @return {*}
    '''
    # 表名：Message
    __tablename__ = 'message'
    # 所有列
    message_id = Column(Integer,primary_key=True,autoincrement=True)
    Sender = Column(String(128))
    Receiver = Column(String(128))
    time = Column(String)
    content = Column(String(1024))

    '''
    @description: 查询结果转换json函数
    @param  {*}
    @return {*}
    @param {*} self
    '''
    def to_json(self):
        dict = self.__dict__
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict

'''
@description: 创建表格函数
@param  {*}
@return {*}
'''
def create_table():
    # 获取Base
    Base,this_engine,message_session=makeSession()
    # 创建所有表格
    Base.metadata.create_all(this_engine)

'''
@description: 添加设备信息函数
@param  {*}
@return {*}
@param {*} username：设备用户名
@param {*} ip：设备IP
'''
def insertDevice(username='yu',ip='192.168.1.2'):
    # 获取session
    Base,this_engine,message_session=makeSession()
    # 查询是否已经存在设备
    resList = message_session.query(Device).filter(Device.ip==ip).all()
    # 不存在则添加
    if len(resList)==0:
        message_session.add(Device(username=username,ip=ip))
        message_session.commit()
    #否则更新
    else:
        message_session.query(Device).filter(Device.ip==ip).update({"username":username})
        message_session.commit()

'''
@description: 获取所有设备函数
@param  {*}
@return {*}
'''
def getAllDevice():
    # 获取session
    Base,this_engine,message_session=makeSession()
    # 获取所有设备
    resList = message_session.query(Device).all()
    print([i.to_json() for i in resList])

'''
@description: 添加信息函数
@param  {*}
@return {*}
@param {*} Sender
@param {*} Receiver
@param {*} time
@param {*} content
'''
def insertMessage(Sender='192.168.1.4', Receiver='192.168.1.2',time='2021-04-07 08:32:00',content='hello world'):
    # 获取session
    Base,this_engine,message_session=makeSession()
    # 插入信息
    message_session.add(Message(Sender=Sender,Receiver=Receiver, time=time,content=content))
    # 数据库提交更改
    message_session.commit()

'''
@description: 获取所有信息函数
@param  {*}
@return {*}
'''
def getAllMessage():
    # 获取Base
    Base,this_engine,message_session=makeSession()
    # 获取所有消息
    resList = message_session.query(Message).all()
    print([i.to_json() for i in resList])

'''
@description: 通过发送方IP查询记录函数
@param  {*}
@return {*}
@param {*} his_ip
'''
def getMessageByHisIPandMySendTo(his_ip):
    # 获取session
    Base,this_engine,message_session=makeSession()
    # 条件查询，发送方为his_ip
    resList = message_session.query(Message).filter(or_(Message.Sender==his_ip, Message.Sender==conf.ip)).order_by(Message.time).all()
    return [i.to_json() for i in resList]

'''
@description: 获取ip对应的用户ID
@param  {*}
@return {*}
@param {*} ip：IP地址
'''
def getSenderId(ip):
    # 获取session
    Base,this_engine,message_session=makeSession()
    # 条件查询，IP为指定IP
    resList = message_session.query(Device).filter(Device.ip==ip).all()
    return [i.to_json() for i in resList][0]['device_id']

# 创建表格，已存在表格则无效果
create_table()

if __name__ == '__main__':
    print(getMessageByHisIPandMySendTo("192.168.1.3"))
